<?php
/**
 * 可发量查询
 * Created by PhpStorm.
 * User: echa
 * Date: 2018/3/1
 * Time: 14:19
 */
namespace App\Services\Warehouse;

use Illuminate\Support\Facades\DB;

class AvailableQueryService extends PublicService{
    public function __construct()
    {
        parent::__construct(config('k3cloud.AvailableQuery'));
    }

    /**
     *  根据商品编号查询和仓库编号查询即时库存
     *   参数说明
     *   $goods_sn   商品货号
     *   $FStockNumber  仓库编号
     *   字段说明
     *  FMaterialId   商品ID
     *  FMaterialId.FNumber  商品货号
     *  FMaterialName  商品名称
     *  FStockId  仓库ID
     *  FStockId.FNumber  仓库编号
     *  FStockName  仓库名称
     *  FBaseQty   库存量
     *  FBaseAVBQty  可用量
     *  FBaseAwaitQty  占用量
     */
    public function GetGoodsAvailableQuery($goods_sn='',$FStockNumber=''){
        if(empty($goods_sn)){
            return false;
        }
        $FieldKeys='FMaterialId,FMaterialId.FNumber,FMaterialName,FStockId,FStockId.FNumber,FStockName,FBaseQty,FBaseAVBQty,FBaseAwaitQty';
        $FilterString="FMaterialId.FNumber='".$goods_sn."'";
        if(!empty($FStockNumber)){
            $FilterString.=" and FStockId.FNumber='".$FStockNumber."'";
        }
        $list=$this->ExecuteBillQuery($FieldKeys,$FilterString);
        if($list){
            return $list;
        }else{
            return false;
        }

    }

    public function GetGoodsWarehouse(array $goods,$FStockNumber){
        if(count($goods)<=0){
            return false;
        }
        if(empty($FStockNumber)){
            return  false;
        }
        $sql="SELECT
      TI.FOWNERID AS FOWNERORGID,
      TI.FOWNERTYPEID AS FOWNERTYPEID,
      V1.FNAME AS FNAMEE,
      V.fnumber AS fnumber,
      M.FMASTERID AS FMATERIALID, -- 物料ID
      M.FNUMBER AS FMATERIALNUMBER, -- 物料编码
      ML.FNAME AS FMATERIALNAME, -- 物料名称
      T2.FFLEXENTRYID AS FFLEXENTRYID,--仓位编码
      T3.FNAME AS FNAMES,--仓位名称
      TSL.FSTOCKID,-- 仓库ID
      TSK.FNUMBER AS FSTOCKNUMBER,-- 仓库编码
      TSL.FNAME AS FSTOCKNAME, -- 仓库名
      TUL0.FUNITID AS FSTOCKUNITID, -- 库存主单位ID
      TU0.FNUMBER AS FSTOCKUNITNUMBER, -- 库存主单位编码
      TUL0.FNAME AS FSTOCKUNITNAME, -- 库存主单位
      STOCK.FNAME AS FNAME,--库存状态名称
      ISNULL( TI.FBASEQTY, 0 ) AS FBASEQTY,-- 库存现有量
      --可用量=库存量-锁库量
      (TI.FBASEQTY-ISNULL((SELECT SUM( TLKE.FBASEQTY )
                           FROM T_PLN_RESERVELINKENTRY TLKE
                             INNER JOIN T_PLN_RESERVELINK TLKH ON TLKE.FID = TLKH.FID
                           WHERE TLKE.FSUPPLYFORMID = 'STK_Inventory'
                                 AND TLKE.FLINKTYPE = '4'
                                 AND TLKE.FSUPPLYINTERID = TI.FID
                                 AND TLKE.FSTOCKID = TI.FSTOCKID
                          ),0)) AS FAVBQTY,
      ISNULL(
          (
            SELECT
              SUM( TLKE.FBASEQTY )
            FROM
              T_PLN_RESERVELINKENTRY TLKE
              INNER JOIN T_PLN_RESERVELINK TLKH ON TLKE.FID = TLKH.FID
            WHERE
              TLKE.FSUPPLYFORMID = 'STK_Inventory'
              AND TLKE.FLINKTYPE = '4'
              AND TLKE.FSUPPLYINTERID = TI.FID
              AND TLKE.FSTOCKID = TI.FSTOCKID
          ),0) AS FBASELOCKQTY,-- 锁库量
      --  待发量= 销售数量 + 累计调拨退货货量 - 累计出库量
      ISNULL((SELECT SUM(DEL.FQTY+DELE.FTRANSRETURNQTY-DEL.FBASESUMOUTQTY)
              FROM T_SAL_DELIVERYNOTICEENTRY DEL
                INNER JOIN T_SAL_DELIVERYNOTICE DELI ON DEL.FID=DELI.FID
                                                        AND DELI.FDOCUMENTSTATUS IN ( 'A','B','C' ) -- 已提交
                                                        AND DELI.FCLOSESTATUS = 'A'
                                                        AND DELI.FCANCELSTATUS = 'A'
    
                INNER JOIN T_SAL_DELIVERYNOTICEENTRY_E DELE ON DEL.FENTRYID=DELE.FENTRYID AND DELE.FOWNERID=TI.FOWNERID
              WHERE M.FMATERIALID=DEL.FMATERIALID AND TSK.FSTOCKID=DEL.FSHIPMENTSTOCKID
             ),0) ASFBASEWAITFORSHIP,
      --可发量=库存量-待发量
      (TI.FBASEQTY-ISNULL((SELECT SUM(DEL.FQTY+DELE.FTRANSRETURNQTY-DEL.FBASESUMOUTQTY)
                           FROM T_SAL_DELIVERYNOTICEENTRY DEL
                             INNER JOIN T_SAL_DELIVERYNOTICE DELI ON DEL.FID=DELI.FID
                                                                     AND DELI.FDOCUMENTSTATUS IN ( 'A','B','C' ) -- 已提交
                                                                     AND DELI.FCLOSESTATUS = 'A'
                                                                     AND DELI.FCANCELSTATUS = 'A'
                             INNER JOIN T_SAL_DELIVERYNOTICEENTRY_E DELE ON DEL.FENTRYID=DELE.FENTRYID AND DELE.FOWNERID=TI.FOWNERID
                           WHERE M.FMATERIALID=DEL.FMATERIALID AND TSK.FSTOCKID=DEL.FSHIPMENTSTOCKID
                          ),0)) FAVAILABLEQTY
    FROM
      T_BD_MATERIAL M
      INNER JOIN -- 物料
      T_STK_INVENTORY TI ON M.FMASTERID = TI.FMATERIALID and TI.FSTOCKSTATUSID = 10000 -- 库存状态可用
                            AND M.FUSEORGID = TI.FSTOCKORGID
      INNER JOIN T_ORG_ORGANIZATIONS O ON M.FUSEORGID = O.FORGID
      LEFT JOIN T_ORG_ORGANIZATIONS_L OL ON M.FUSEORGID = OL.FORGID
                                            AND OL.FLOCALEID = 2052 -- 机构名称
      LEFT JOIN T_BD_MATERIAL_L ML ON M.FMATERIALID = ML.FMATERIALID
                                      AND ML.FLOCALEID = 2052 -- 物料名称、规格型号
      INNER JOIN T_BD_MATERIALSTOCK TMS ON M.FMATERIALID = TMS.FMATERIALID
      LEFT JOIN T_BD_STOCKSTATUS_L TSSL ON TI.FSTOCKSTATUSID = TSSL.FSTOCKSTATUSID
                                           AND TSSL.FLOCALEID = 2052 AND TSSL.FSTOCKSTATUSID = 10000
      LEFT JOIN V_ITEMCLASS_OWNER VO ON VO.FMASTERID=TI.FOWNERID AND VO.FFORMID=TI.FOWNERTYPEID AND TI.FOWNERTYPEID='BD_OwnerOrg' -- 货主
      LEFT JOIN T_ORG_ORGANIZATIONS O2 ON TI.FOWNERID = O2.FORGID
      LEFT JOIN T_ORG_ORGANIZATIONS_L OL2 ON TI.FOWNERID = OL2.FORGID
                                             AND OL2.FLOCALEID = 2052 -- 机构名称
      LEFT JOIN T_BD_UNIT_L TUL0 ON TMS.FSTOREUNITID = TUL0.FUNITID
                                    AND TUL0.FLOCALEID = 2052
      LEFT JOIN T_BD_UNIT TU0 ON TMS.FSTOREUNITID = TU0.FUNITID
      LEFT JOIN T_BD_STOCK TSK ON ( TSK.FMASTERID = TI.FSTOCKID AND TSK.FUSEORGID = TI.FSTOCKORGID )
      LEFT JOIN T_BD_STOCK_L TSL ON TSL.FSTOCKID = TI.FSTOCKID
                                    AND TSL.FLOCALEID = 2052 -- 语言中文
      LEFT JOIN T_BD_UNIT_L TUL1 ON TI.FBASEUNITID = TUL1.FUNITID
                                    AND TUL1.FLOCALEID = 2052
      LEFT JOIN T_BD_UNIT TU1 ON TI.FBASEUNITID = TU1.FUNITID
      LEFT JOIN t_BD_StockStatus_L STOCK ON TI.FSTOCKSTATUSID=STOCK.FSTOCKSTATUSID AND STOCK.FLOCALEID = 2052
      LEFT JOIN T_BD_STOCKFLEXITEM T1 ON TSK.FSTOCKID=T1.FSTOCKID
      LEFT JOIN T_BD_STOCKFLEXDETAIL T2 ON T1.FENTRYID=T2.FENTRYID
      LEFT JOIN T_BAS_FLEXVALUESENTRY_L T3 ON T2.FDETAILID=T3.FENTRYID AND T3.FLOCALEID = 2052
      LEFT JOIN v_itemclass_owner V ON TI.FOWNERID=V.fmasterid
      LEFT JOIN v_itemclass_owner_L V1 ON V.fitemid=V1.fitemid AND V1.FLOCALEID = 2052
      WHERE ";
        $where="";
        $FOwnerTypeBDSupplier=config('k3cloud.FOwnerTypeBDSupplier');
        $FOwnerTypeBDOwnerOrg=config('k3cloud.FOwnerTypeBDOwnerOrg');
        foreach ($goods as $k=>$v){
            if(count($v)<=0){
                continue;
            }
            $FOwnerFNumber=config('k3cloud.organization');
            if($v['k3_vmi']){
                $FOwnerTypeID=$FOwnerTypeBDSupplier;
                $FOwnerFNumber=$v['k3_supplier_sn'];
            }else{
                $FOwnerTypeID=$FOwnerTypeBDOwnerOrg;
            }
            $where.="(V1.FNAME<>'' AND M.FNUMBER='".$v['goods_sn']."' AND TSK.FNUMBER='".$FStockNumber."' AND TI.FOWNERTYPEID='".$FOwnerTypeID."' AND V.fnumber='".$FOwnerFNumber."') OR ";
        }
        $where=substr($where,0,strlen($where)-3);
        $sql.=$where;
        //return DB::connection('sqlsrv')->select("exec GoodsWarehouseDetail ?,?",[$goods_sn_str,$FStockNumber]);
        return DB::connection('sqlsrv')->select($sql);
    }

    public function GetGoodsWarehouseFSTOCKNUMBER(array $goods,$FStockNumber){
        if(count($goods)<=0){
            return false;
        }
        if(empty($FStockNumber)){
            return  false;
        }
        $sql="SELECT 
  M.FNUMBER AS FMATERIALNUMBER, -- 物料编码
  --可发量=库存量-待发量
  (TI.FBASEQTY-ISNULL((SELECT SUM(DEL.FQTY+DELE.FTRANSRETURNQTY-DEL.FBASESUMOUTQTY)
                       FROM T_SAL_DELIVERYNOTICEENTRY DEL
                         INNER JOIN T_SAL_DELIVERYNOTICE DELI ON DEL.FID=DELI.FID
                                                                 AND DELI.FDOCUMENTSTATUS IN ( 'A','B','C' ) -- 已提交
                                                                 AND DELI.FCLOSESTATUS = 'A'
                                                                 AND DELI.FCANCELSTATUS = 'A'
                         INNER JOIN T_SAL_DELIVERYNOTICEENTRY_E DELE ON DEL.FENTRYID=DELE.FENTRYID AND DELE.FOWNERID=TI.FOWNERID
                       WHERE M.FMATERIALID=DEL.FMATERIALID AND TSK.FSTOCKID=DEL.FSHIPMENTSTOCKID
                      ),0)) FAVAILABLEQTY
FROM
  T_BD_MATERIAL M
  INNER JOIN -- 物料
  T_STK_INVENTORY TI ON M.FMASTERID = TI.FMATERIALID and TI.FSTOCKSTATUSID = 10000 -- 库存状态可用
  INNER JOIN T_BD_MATERIALSTOCK TMS ON M.FMATERIALID = TMS.FMATERIALID
  LEFT JOIN V_ITEMCLASS_OWNER VO ON VO.FMASTERID=TI.FOWNERID AND VO.FFORMID=TI.FOWNERTYPEID AND TI.FOWNERTYPEID='BD_OwnerOrg' -- 货主
  LEFT JOIN T_BD_STOCK TSK ON ( TSK.FMASTERID = TI.FSTOCKID AND TSK.FUSEORGID = TI.FSTOCKORGID )
  LEFT JOIN v_itemclass_owner V ON TI.FOWNERID=V.fmasterid
  LEFT JOIN v_itemclass_owner_L V1 ON V.fitemid=V1.fitemid AND V1.FLOCALEID = 2052 
  WHERE ";
        $where="";
        $FOwnerTypeBDSupplier=config('k3cloud.FOwnerTypeBDSupplier');
        $FOwnerTypeBDOwnerOrg=config('k3cloud.FOwnerTypeBDOwnerOrg');
        foreach ($goods as $k=>$v){
            if(count($v)<=0){
                continue;
            }
            $FOwnerFNumber=config('k3cloud.organization');
            if($v['k3_vmi']){
                $FOwnerTypeID=$FOwnerTypeBDSupplier;
                $FOwnerFNumber=$v['k3_supplier_sn'];
            }else{
                $FOwnerTypeID=$FOwnerTypeBDOwnerOrg;
            }
            $where.="(V1.FNAME<>'' AND M.FNUMBER='".$v['goods_sn']."' AND TSK.FNUMBER='".$FStockNumber."' AND TI.FOWNERTYPEID='".$FOwnerTypeID."' AND V.fnumber='".$FOwnerFNumber."') OR ";
        }
        $where=substr($where,0,strlen($where)-3);
        $sql.=$where;
        //return DB::connection('sqlsrv')->select("exec GoodsWarehouseDetail ?,?",[$goods_sn_str,$FStockNumber]);
        return DB::connection('sqlsrv')->select($sql);
    }
}